from datetime import datetime
from threading import Thread
from tkinter import ttk, messagebox
import sys
import pyodbc
import tkinter as tk


class LibraryManagementSystem:
    def __init__(self):
        # 连接到 SQL Server 数据库
        self.conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=sjk;UID=sa;PWD=123456')

        # 只有第一次运行取消以下注释
        # 创建管理员和学生表
        self.create_admin_table()
        self.create_student_table()

        # 创建图书表
        self.create_books_table()
        self.create_books_constraint()

        # 创建借阅记录表
        self.create_borrow_records_table()

        self.create_trigger()

    def create_admin_table(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE  Admin (
                username VARCHAR(255) PRIMARY KEY,
                password VARCHAR(255)
            )
        ''')
        self.conn.commit()
        cursor = self.conn.cursor()
        cursor.execute('''
            INSERT
            INTO
            Admin(username, password)
            VALUES('admin', 'admin');   
        ''')
        self.conn.commit()

    # INSERT
    # INTO
    # Admin(username, password)
    # VALUES('admin', 'admin');

    def create_student_table(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE Student (
                student_id INT PRIMARY KEY,
                name VARCHAR(255),
                username VARCHAR(255),
                password VARCHAR(255)
            )
        ''')
        self.conn.commit()
        cursor = self.conn.cursor()
        cursor.execute('''
            INSERT
            INTO
            Student(student_id, name, username, password)
            VALUES
            (1001, '张三', '1001', 'password1'),
            (1002, '李四', '1002', 'password2'),
            (1003, '王五', '1003', 'password3'),
            (1004, '赵六', '1004', 'password4'),
            (1005, '陈七', '1005', 'password5'),
            (1006, '刘八', '1006', 'password6'),
            (1007, '孙九', '1007', 'password7'),
            (1008, '周十', '1008', 'password8'),
            (1009, '吴十一', '1009', 'password9'),
            (1010, '郑十二', '1010', 'password10');
        ''')
        self.conn.commit()

    # INSERT
    # INTO
    # Student(student_id, name, username, password)
    # VALUES
    # (1001, '张三', '1001', 'password1'),
    # (1002, '李四', '1002', 'password2'),
    # (1003, '王五', '1003', 'password3'),
    # (1004, '赵六', '1004', 'password4'),
    # (1005, '陈七', '1005', 'password5'),
    # (1006, '刘八', '1006', 'password6'),
    # (1007, '孙九', '1007', 'password7'),
    # (1008, '周十', '1008', 'password8'),
    # (1009, '吴十一', '1009', 'password9'),
    # (1010, '郑十二', '1010', 'password10');

    def create_books_table(self):
        cursor = self.conn.cursor()
        cursor.execute('''
                CREATE TABLE Books (  
                    book_id INT PRIMARY KEY,  
                    title VARCHAR(255) NOT NULL,  
                    author VARCHAR(255) NOT NULL,  
                    price FLOAT,  
                    publisher VARCHAR(255),  
                    quantity INT,  
                    category VARCHAR(255)  
                );
        ''')
        self.conn.commit()
        cursor = self.conn.cursor()
        cursor.execute('''    
            INSERT
            INTO
            Books(book_id, title, author, price, publisher, quantity, category)
            VALUES
            (1, '活着', '余华', 29.99, '作家出版社', 10, '小说'),
            (2, '围城', '钱钟书', 39.99, '商务印书馆', 5, '小说'),
            (3, '红楼梦', '曹雪芹', 49.99, '人民文学出版社', 8, '小说'),
            (4, '白夜行', '东野圭吾', 24.99, '南海出版公司', 12, '悬疑'),
            (5, '解忧杂货店', '东野圭吾', 26.99, '南海出版公司', 15, '悬疑'),
            (6, '三体', '刘慈欣', 59.99, '重庆出版社', 3, '科幻'),
            (7, '平凡的世界', '路遥', 34.99, '人民文学出版社', 6, '小说'),
            (8, '人类简史', '尤瓦尔·赫拉利', 45.99, '中信出版社', 7, '历史'),
            (9, '活法', '罗辑思维', 19.99, '浙江人民出版社', 9, '经济'),
            (10, '围城', '钱钟书', 39.99, '商务印书馆', 5, '小说'),
            (11, '百年孤独', '加西亚·马尔克斯', 36.99, '南海出版公司', 10, '小说'),
            (12, '1984', '乔治·奥威尔', 28.99, '机械工业出版社', 8, '科幻'),
            (13, '挪威的森林', '村上春树', 32.99, '上海译文出版社', 12, '小说'),
            (14, '平凡的世界', '路遥', 34.99, '人民文学出版社', 6, '小说'),
            (15, '追风筝的人', '卡勒德·胡赛尼', 30.99, '上海人民出版社', 9, '小说'),
            (16, '霍乱时期的爱情', '加西亚·马尔克斯', 33.99, '南海出版公司', 5, '小说'),
            (17, '明朝那些事儿', '当年明月', 25.99, '湖南文艺出版社', 7, '历史'),
            (18, '三国演义', '罗贯中', 28.99, '人民文学出版社', 10, '历史'),
            (19, '哈利·波特与魔法石', 'J.K.罗琳', 29.99, '中国青年出版社', 8, '奇幻'),
            (20, '心理学与生活', '理查德·格里格', 42.99, '中国人民大学出版社', 6, '心理学');
                ''')
        self.conn.commit()

    # INSERT
    # INTO
    # Books(book_id, title, author, price, publisher, quantity, category)
    # VALUES
    # (1, '活着', '余华', 29.99, '作家出版社', 10, '小说'),
    # (2, '围城', '钱钟书', 39.99, '商务印书馆', 5, '小说'),
    # (3, '红楼梦', '曹雪芹', 49.99, '人民文学出版社', 8, '小说'),
    # (4, '白夜行', '东野圭吾', 24.99, '南海出版公司', 12, '悬疑'),
    # (5, '解忧杂货店', '东野圭吾', 26.99, '南海出版公司', 15, '悬疑'),
    # (6, '三体', '刘慈欣', 59.99, '重庆出版社', 3, '科幻'),
    # (7, '平凡的世界', '路遥', 34.99, '人民文学出版社', 6, '小说'),
    # (8, '人类简史', '尤瓦尔·赫拉利', 45.99, '中信出版社', 7, '历史'),
    # (9, '活法', '罗辑思维', 19.99, '浙江人民出版社', 9, '经济'),
    # (10, '围城', '钱钟书', 39.99, '商务印书馆', 5, '小说'),
    # (11, '百年孤独', '加西亚·马尔克斯', 36.99, '南海出版公司', 10, '小说'),
    # (12, '1984', '乔治·奥威尔', 28.99, '机械工业出版社', 8, '科幻'),
    # (13, '挪威的森林', '村上春树', 32.99, '上海译文出版社', 12, '小说'),
    # (14, '平凡的世界', '路遥', 34.99, '人民文学出版社', 6, '小说'),
    # (15, '追风筝的人', '卡勒德·胡赛尼', 30.99, '上海人民出版社', 9, '小说'),
    # (16, '霍乱时期的爱情', '加西亚·马尔克斯', 33.99, '南海出版公司', 5, '小说'),
    # (17, '明朝那些事儿', '当年明月', 25.99, '湖南文艺出版社', 7, '历史'),
    # (18, '三国演义', '罗贯中', 28.99, '人民文学出版社', 10, '历史'),
    # (19, '哈利·波特与魔法石', 'J.K.罗琳', 29.99, '中国青年出版社', 8, '奇幻'),
    # (20, '心理学与生活', '理查德·格里格', 42.99, '中国人民大学出版社', 6, '心理学');

    def create_books_constraint(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            ALTER TABLE Books
            ADD CONSTRAINT no_author_change CHECK (author = author);
            ALTER TABLE Books
            ADD CONSTRAINT no_id_change CHECK (book_id = book_id);
        ''')
        self.conn.commit()

    def create_borrow_records_table(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE  BorrowRecords (
                record_id INT PRIMARY KEY,
                book_id INT,
                title VARCHAR(255),
                student_id INT,
                name VARCHAR(255),
                borrow_date DATE,
                return_date DATE,
                FOREIGN KEY (book_id) REFERENCES Books(book_id),
                FOREIGN KEY (student_id) REFERENCES Student(student_id)
            )
        ''')
        self.conn.commit()
        cursor = self.conn.cursor()
        cursor.execute('''
            INSERT INTO BorrowRecords (record_id, book_id, title, student_id, name, borrow_date, return_date) 
            VALUES 
            (1, 1, '活着', 1001, '张三', '2023-12-01', '2023-12-11'),
            (2, 1, '围城', 1001, '张三', '2023-06-08', NULL);

        ''')
        self.conn.commit()

    # INSERT
    # INTO
    # BorrowRecords(record_id, book_id, title, student_id, name, borrow_date, return_date)
    # VALUES
    # (1, 1, '活着', 1001, '张三', '2023-12-01', '2023-12-11'),
    # (2, 1, '围城', 1001, '张三', '2023-06-08', NULL);

    def create_trigger(self):
        cursor = self.conn.cursor()
        # 创建触发器
        trigger_sql = """
        CREATE TRIGGER trgCheckOverdue
        ON BorrowRecords
        FOR UPDATE
        AS
        BEGIN
            IF EXISTS (
                SELECT *
                FROM inserted i
                WHERE i.return_date < GETDATE()
            )
            BEGIN
                DECLARE @record_id int, @name varchar(255)
                SELECT @record_id = i.record_id, @name = i.name
                FROM inserted i
                WHERE i.return_date < GETDATE()

                RAISERROR (N'本书已过期，请先交罚款。借阅记录ID：%d，学生姓名：%s', 16, 1, @record_id, @name)
            END
        END
        """
        cursor.execute(trigger_sql)
        self.conn.commit()

    # 借书
    def borrow_book(self, book_id):
        cursor = self.conn.cursor()

        # 获取当前最大的record_id
        cursor.execute('SELECT MAX(record_id) FROM BorrowRecords')
        max_record_id = cursor.fetchone()[0]
        next_record_id = max_record_id + 1 if max_record_id is not None else 1

        # 检查书籍是否可借
        cursor.execute('SELECT * FROM Books WHERE book_id=? AND quantity > 0', (book_id,))
        book = cursor.fetchone()
        title = book[1]

        cursor.execute('SELECT * FROM Student WHERE student_id=?', (self.username_entry,))
        name = cursor.fetchone()[1]

        if book:
            # 更新图书数量和借阅记录
            cursor.execute('UPDATE Books SET quantity = quantity - 1 WHERE book_id=?', (book_id,))
            cursor.execute(
                'INSERT INTO BorrowRecords (record_id, book_id, title, student_id, name,borrow_date) VALUES (?,?,?, '
                '?, ?, ?)',
                (next_record_id, book_id, title, self.username_entry, name, datetime.now()))
            self.conn.commit()
            print('借阅成功')
        else:
            print('借阅失败，该书籍已无库存')

    # 还书
    def return_book(self, book_id):
        cursor = self.conn.cursor()
        try:
            # 检查借阅记录是否存在
            cursor.execute('SELECT * FROM BorrowRecords WHERE book_id=? AND student_id=? AND return_date IS NULL',
                           (book_id, self.username_entry))
            borrow_record = cursor.fetchone()
            if borrow_record:
                return_date = datetime.now()
                # 更新图书数量和借阅记录
                cursor.execute('UPDATE Books SET quantity = quantity + 1 WHERE book_id=?', (book_id,))

                cursor.execute("UPDATE BorrowRecords SET return_date =? WHERE book_id=? AND student_id=?",
                               (return_date, book_id, self.username_entry))
                # 提交事务
                self.conn.commit()
                print('归还成功')
            else:
                print('归还失败，未找到借阅记录')
        except pyodbc.Error as e:
            error_message = str(e)
            print(error_message)

    def return_book_admin(self, student_id, book_id):
        cursor = self.conn.cursor()

        # 检查借阅记录是否存在
        cursor.execute('SELECT * FROM BorrowRecords WHERE book_id=? AND student_id=? AND return_date IS NULL',
                       (book_id, student_id))
        borrow_record = cursor.fetchone()
        if borrow_record:
            return_date = datetime.now()

            # 禁用触发器
            disable_trigger_sql = """
            DISABLE TRIGGER trgCheckOverdue ON BorrowRecords
            """
            cursor.execute(disable_trigger_sql)

            cursor.execute('UPDATE Books SET quantity = quantity + 1 WHERE book_id=?', (book_id,))

            cursor.execute("UPDATE BorrowRecords SET return_date =? WHERE book_id=? AND student_id=?",
                           (return_date, book_id, student_id))
            self.conn.commit()

            # 启用触发器
            enable_trigger_sql = """
            ENABLE TRIGGER trgCheckOverdue ON BorrowRecords
            """
            cursor.execute(enable_trigger_sql)
            # 提交事务
            self.conn.commit()
            print('罚款已交，归还成功')
        else:
            print('归还失败，未找到借阅记录')

    def show_borrow_records(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM BorrowRecords')
        borrow_records = cursor.fetchall()
        print("查看所有借阅记录：")
        print("序号\t\t书籍\t\t借阅人\t\t借阅日期\t\t归还日期")
        for i, record in enumerate(borrow_records, start=1):
            print(f'{i}\t\t《{record.title}》\t\t{record.name}\t\t{record.borrow_date}\t\t{record.return_date}')
        self.conn.commit()

    def grant_stu(self, student_id):
        cursor = self.conn.cursor()
        cursor.execute(
            f"INSERT INTO Admin (username, password) SELECT student_id, password FROM Student WHERE student_id = '{student_id}'")
        cursor.execute(f"DELETE FROM BorrowRecords WHERE student_id = '{student_id}'")
        cursor.execute(f"DELETE FROM Student WHERE student_id = '{student_id}'")
        self.conn.commit()
        print("相关借阅记录已删除，管理权限授予成功")

    def show_books(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM Books')
        books = cursor.fetchall()
        print("查看所有书籍：")
        print("书籍编号\t书名\t\t\t作者\t\t\t价格\t\t出版社\t\t\t数量\t\t分类")
        for record in books:
            print(f'{record[0]}\t《{record[1]}》\t\t\t{record[2]}\t\t\t'
                  f'{record[3]}\t\t{record[4]}\t\t\t{record[5]}\t\t{record[6]}')
        self.conn.commit()

    def show_student(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM Student')
        stus = cursor.fetchall()
        print("查看学生：")
        print("学生ID\t\t姓名\t\t\t借阅证\t\t\t密码")
        for record in stus:
            print(f'{record[0]}\t\t{record[1]}\t\t\t{record[2]}\t\t\t{record[3]}')
        self.conn.commit()

    # 登录功能
    def login(self, username, password, account_type):
        cursor = self.conn.cursor()
        if account_type == 'admin':
            cursor.execute('SELECT * FROM Admin WHERE username=? AND password=?', (username, password))
            result = cursor.fetchone()
            if result:
                print('管理员登录成功')
                return True
            else:
                return False
        elif account_type == 'student':
            cursor.execute('SELECT * FROM Student WHERE username=? AND password=?', (username, password))
            result = cursor.fetchone()
            if result:
                print('学生登录成功')
                self.username_entry = username
                return True
            else:
                print('登录失败')
                return False
        self.conn.commit()

    def delete_student(self, student_id):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM Student WHERE student_id=?',
                       student_id)
        student = cursor.fetchone()
        if student:
            cursor.execute('DELETE FROM Student WHERE student_id=?',
                           student_id)
            print("账户已删除")
        else:
            print('账户不存在')
        self.conn.commit()

    # 学生注册
    def register_student(self, student_id, name, username, password):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM Student WHERE student_id=?',
                       student_id)
        student = cursor.fetchone()
        if student:
            print("学生已存在，请登录")
        else:
            # 执行插入语句
            sql = "INSERT INTO student (student_id, name, username, password) VALUES (?, ?, ?, ?)"
            cursor.execute(sql, (student_id, name, username, password))
            print('学生注册成功')
        self.conn.commit()

    # 增加书籍
    def add_book(self, book_id, title, author, price, publisher, quantity, category):
        cursor = self.conn.cursor()
        cursor.execute(
            'INSERT INTO Books (book_id, title, author, price, publisher, quantity, category) VALUES (?, ?, ?, ?, ?, ?, ?)',
            (book_id, title, author, price, publisher, quantity, category))
        self.conn.commit()
        print('图书添加成功')

    # 删除书籍
    def delete_book(self, book_id):
        cursor = self.conn.cursor()
        cursor.execute('DELETE FROM Books WHERE book_id=?', (book_id,))
        self.conn.commit()
        print('图书删除成功')

    def edit_book(self, book_id, title, author, publisher, price, quantity, category):
        cursor = self.conn.cursor()
        # 构建 SQL 查询语句
        sql = "UPDATE books SET book_id=?,title=?, author=?, price=?, publisher=?, quantity=?, category=? WHERE book_id=?"
        # 执行 SQL 查询语句
        cursor.execute(sql, (book_id, title, author, price, publisher, quantity, category, book_id))
        cursor.execute('SELECT * FROM Books WHERE book_id=?', (book_id,))
        books = cursor.fetchall()
        print("修改后为：", books)
        self.conn.commit()

        # 提示用户修改成功
        tk.messagebox.showinfo("修改成功", "已成功修改图书信息！")

    # 搜书
    def search_books(self, keyword, search_type):
        cursor = self.conn.cursor()
        cursor.execute(f"SELECT * FROM Books WHERE {search_type} LIKE '%{keyword}%'")
        results = cursor.fetchall()
        if results:
            print('搜索结果：')
            for row in results:
                print(row)
        else:
            print('未找到相关图书')
        self.conn.commit()

    # 搜学生
    def search_students(self, keyword, search_type):
        cursor = self.conn.cursor()
        cursor.execute(f"SELECT * FROM Student WHERE {search_type} LIKE '%{keyword}%'")
        results = cursor.fetchall()
        if results:
            print('搜索结果：')
            for row in results:
                print(row)
        else:
            print('未找到相关学生')
        self.conn.commit()

    def search_borrow_records_by_student_id(self, student_id):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM BorrowRecords WHERE student_id=?', (student_id,))
        results = cursor.fetchall()
        if results:
            print('借阅记录：')
            for row in results:
                print(row)
        else:
            print('未找到借阅记录')
        self.conn.commit()

    def search_borrow_records_by_book_id(self, book_id):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM BorrowRecords WHERE book_id=?', (book_id,))
        results = cursor.fetchall()
        if results:
            print('借阅记录：')
            for row in results:
                print(row)
        else:
            print('未找到借阅记录')
        self.conn.commit()

    # 分类搜书
    def search_book_quantity_by_category(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT category, COUNT(*) FROM Books GROUP BY category')
        results = cursor.fetchall()

        if results:
            print('图书分类统计结果：')
            print("类别\t数量")
            for row in results:
                print(row[0], "\t", row[1])
        else:
            print('未找到相关图书')


class OutputWindow:
    def __init__(self):
        # 创建主窗口
        self.root = tk.Tk()
        self.root.title("程序输出窗口")
        self.root.geometry("1200x600")

        # 添加Text控件
        self.text = tk.Text(self.root, height=50, width=200)
        self.text.pack()

        # 将print函数重定向到text控件
        sys.stdout = self

        # 监听窗口关闭事件
        self.root.protocol("WM_DELETE_WINDOW", self.on_close)

    def on_close(self):
        # 关闭窗口时退出应用程序
        self.root.destroy()
        sys.exit()

    def write(self, message):
        # 将所有输出写入text控件
        self.text.insert(tk.END, message)
        self.root.deiconify()  # 将窗口弹到窗口顶部

    def flush(self):
        pass

    def run(self):
        # 运行主循环
        self.root.mainloop()

    def clear_text(self):
        self.text.delete(1.0, tk.END)

    def destroy(self):
        self.root.destroy()


class GUI:

    def __init__(self, lms):
        # 创建输出窗口
        self.output_window = OutputWindow()

        # 在输出窗口中输出一些内容
        print("输出：")

        # 使用线程运行输出窗口
        thread = Thread(target=self.output_window.run)
        thread.start()

        self.lms = lms

        self.root = tk.Tk()
        self.root.title("图书借阅管理系统")
        self.root.geometry("1000x800")

        # 登录界面
        self.login_frame = tk.Frame(self.root)
        self.login_frame.pack()

        self.login_label = tk.Label(self.login_frame, text="登录", font=("Arial", 20))
        self.login_label.pack(pady=20)

        self.username_label = tk.Label(self.login_frame, text="用户名：")
        self.username_label.pack()

        self.username_entry = tk.Entry(self.login_frame)
        self.username_entry.pack()

        self.password_label = tk.Label(self.login_frame, text="密码：")
        self.password_label.pack()

        self.password_entry = tk.Entry(self.login_frame, show="*")
        self.password_entry.pack()

        # 创建框架来容纳注册和登录按钮
        self.login_button_frame = tk.Frame(self.login_frame)
        self.login_button_frame.pack(side=tk.TOP, pady=20)

        self.register = tk.Button(self.login_button_frame, text="注册", command=self.register)
        self.register.grid(row=0, column=0, padx=40)

        self.login_button = tk.Button(self.login_button_frame, text="登录", command=self.login)
        self.login_button.grid(row=0, column=1, padx=40)

        self.exit_button = tk.Button(self.login_frame, text="退出", command=self.exit)
        self.exit_button.pack(side=tk.BOTTOM, pady=20)

        # 管理员界面
        self.admin_frame = tk.Frame(self.root)

        self.admin_book_frame = tk.Frame(self.admin_frame)
        self.admin_book_frame.pack(side=tk.LEFT, padx=40, pady=20)

        self.admin_add_book_label = tk.Label(self.admin_book_frame, text="添加图书")
        self.admin_add_book_label.pack()

        self.book_id_label = tk.Label(self.admin_book_frame, text="图书编号：")
        self.book_id_label.pack()

        self.book_id_entry = tk.Entry(self.admin_book_frame)
        self.book_id_entry.pack()

        self.title_label = tk.Label(self.admin_book_frame, text="书名：")
        self.title_label.pack()

        self.title_entry = tk.Entry(self.admin_book_frame)
        self.title_entry.pack()

        self.author_label = tk.Label(self.admin_book_frame, text="作者：")
        self.author_label.pack()

        self.author_entry = tk.Entry(self.admin_book_frame)
        self.author_entry.pack()

        self.price_label = tk.Label(self.admin_book_frame, text="价格：")
        self.price_label.pack()

        self.price_entry = tk.Entry(self.admin_book_frame)
        self.price_entry.pack()

        self.publisher_label = tk.Label(self.admin_book_frame, text="出版社：")
        self.publisher_label.pack()

        self.publisher_entry = tk.Entry(self.admin_book_frame)
        self.publisher_entry.pack()

        self.quantity_label = tk.Label(self.admin_book_frame, text="数量：")
        self.quantity_label.pack()

        self.quantity_entry = tk.Entry(self.admin_book_frame)
        self.quantity_entry.pack()

        self.category_label = tk.Label(self.admin_book_frame, text="分类：")
        self.category_label.pack()

        self.category_entry = tk.Entry(self.admin_book_frame)
        self.category_entry.pack()

        self.add_book_button = tk.Button(self.admin_book_frame, text="添加图书", command=self.add_book)
        self.add_book_button.pack(pady=10)

        self.admin_delete_book_label = tk.Label(self.admin_book_frame, text="删除图书")
        self.admin_delete_book_label.pack()

        self.delete_book_id_label = tk.Label(self.admin_book_frame, text="图书编号：")
        self.delete_book_id_label.pack()

        self.delete_book_id_entry = tk.Entry(self.admin_book_frame)
        self.delete_book_id_entry.pack()

        self.delete_book_button = tk.Button(self.admin_book_frame, text="删除图书", command=self.delete_book)
        self.delete_book_button.pack(pady=10)

        self.edit_label = tk.Label(self.admin_book_frame, text="修改图书")
        self.edit_label.pack()

        self.edit_label1 = tk.Label(self.admin_book_frame, text="图书编号：")
        self.edit_label1.pack()

        self.edit_book_id_entry = tk.Entry(self.admin_book_frame)
        self.edit_book_id_entry.pack()

        self.edit_book_button = tk.Button(self.admin_book_frame, text="修改图书信息", command=self.edit_book)
        self.edit_book_button.pack(pady=10)

        self.show_book_button = tk.Button(self.admin_book_frame, text="查看全部书籍", command=self.show_book)
        self.show_book_button.pack(pady=20)

        self.student_search_book_quantity_frame = tk.Frame(self.admin_frame)
        self.student_search_book_quantity_frame.pack()

        self.student_search_book_quantity_label = tk.Label(self.student_search_book_quantity_frame,
                                                           text="搜索图书库存情况")
        self.student_search_book_quantity_label.pack()

        self.search_book_quantity_button = tk.Button(self.student_search_book_quantity_frame, text="搜索",
                                                     command=self.search_book_quantity)
        self.search_book_quantity_button.pack(pady=10)
        self.show_stu = tk.Button(self.admin_frame, text="学生列表", command=self.show_stus)
        self.show_stu.pack(pady=20)

        self.student_search_students_frame = tk.Frame(self.admin_frame)
        self.student_search_students_frame.pack()

        self.student_search_students_label = tk.Label(self.student_search_students_frame, text="搜索学生")
        self.student_search_students_label.pack()

        self.search_students_keyword_label = tk.Label(self.student_search_students_frame, text="关键词：")
        self.search_students_keyword_label.pack()

        self.search_students_keyword_entry = tk.Entry(self.student_search_students_frame)
        self.search_students_keyword_entry.pack()

        self.search_students_type_label = tk.Label(self.student_search_students_frame, text="搜索类型：")
        self.search_students_type_label.pack()

        self.search_students_type_combobox = ttk.Combobox(self.student_search_students_frame,
                                                          values=["姓名", "借书证号"], state="readonly")
        self.search_students_type_combobox.current(0)
        self.search_students_type_combobox.pack()

        self.search_students_button = tk.Button(self.student_search_students_frame, text="搜索",
                                                command=self.search_students)
        self.search_students_button.pack(pady=10)

        self.delete_stu_label = tk.Label(self.admin_frame, text="删除学生账号")
        self.delete_stu_label.pack()

        self.delete_stu_label1 = tk.Label(self.admin_frame, text="借阅证号：")
        self.delete_stu_label1.pack()

        self.delete_stu_entry = tk.Entry(self.admin_frame)
        self.delete_stu_entry.pack()

        self.delete_stu_button = tk.Button(self.admin_frame, text="删除学生账户", command=self.delete_stu)
        self.delete_stu_button.pack(pady=20)

        self.grant_button0 = tk.Button(self.admin_frame, text="管理权限授予", command=self.grant0)
        self.grant_button0.pack(pady=20)

        self.return_admin_button = tk.Button(self.admin_frame, text="过期还书", command=self.return_admin)
        self.return_admin_button.pack(pady=20)

        self.show_records_button = tk.Button(self.admin_frame, text="借阅记录", command=self.show_records)
        self.show_records_button.pack(pady=20)

        self.logout_button = tk.Button(self.admin_frame, text="退出登录", command=self.logout)
        self.logout_button.pack(pady=20)

        # 学生界面
        self.student_frame = tk.Frame(self.root)

        self.student_search_books_frame = tk.Frame(self.student_frame)
        self.student_search_books_frame.grid(row=0, column=0, pady=20)

        self.student_search_books_label = tk.Label(self.student_search_books_frame, text="搜索图书")
        self.student_search_books_label.pack()
        self.search_books_keyword_label = tk.Label(self.student_search_books_frame, text="关键词：")
        self.search_books_keyword_label.pack()

        self.search_books_keyword_entry = tk.Entry(self.student_search_books_frame)
        self.search_books_keyword_entry.pack()

        self.search_books_type_label = tk.Label(self.student_search_books_frame, text="搜索类型：")
        self.search_books_type_label.pack()

        self.search_books_type_combobox = ttk.Combobox(self.student_search_books_frame,
                                                       values=["书名", "作者", "价格", "出版社"], state="readonly")
        self.search_books_type_combobox.current(0)
        self.search_books_type_combobox.pack()

        self.search_books_button = tk.Button(self.student_search_books_frame, text="搜索", command=self.search_books)
        self.search_books_button.pack(pady=10)

        self.student_search_borrow_records_frame = tk.Frame(self.student_frame)
        self.student_search_borrow_records_frame.grid(row=1, column=0, pady=20)

        self.student_search_borrow_records_label = tk.Label(self.student_search_borrow_records_frame,
                                                            text="搜索借阅记录")
        self.student_search_borrow_records_label.pack()

        self.search_borrow_records_student_id_label = tk.Label(self.student_search_borrow_records_frame,
                                                               text="借书证号：")
        self.search_borrow_records_student_id_label.pack()

        self.search_borrow_records_student_id_entry = tk.Entry(self.student_search_borrow_records_frame)
        self.search_borrow_records_student_id_entry.pack()

        self.search_borrow_records_button = tk.Button(self.student_search_borrow_records_frame, text="搜索",
                                                      command=self.search_borrow_records)
        self.search_borrow_records_button.pack(pady=10)

        self.student_search_book_quantity_frame = tk.Frame(self.student_frame)
        self.student_search_book_quantity_frame.grid(row=1, column=1, pady=20)

        self.student_search_book_quantity_label = tk.Label(self.student_search_book_quantity_frame,
                                                           text="搜索图书库存情况")
        self.student_search_book_quantity_label.pack()

        self.search_book_quantity_button = tk.Button(self.student_search_book_quantity_frame, text="搜索",
                                                     command=self.search_book_quantity)
        self.search_book_quantity_button.pack(pady=10)

        self.borrow_label = tk.Label(self.student_frame, text="输入需要借阅书籍的编号")
        self.borrow_label.grid(row=2, column=0)
        self.borrow_entry = tk.Entry(self.student_frame)
        self.borrow_entry.grid(row=2, column=1)
        self.borrow = tk.Button(self.student_frame, text="借书", command=self.borrow)
        self.borrow.grid(row=3, column=0, pady=20)

        self.return_label = tk.Label(self.student_frame, text="输入需要归还书籍的编号")
        self.return_label.grid(row=4, column=0)
        self.return_entry = tk.Entry(self.student_frame)
        self.return_entry.grid(row=4, column=1)
        self.return_book = tk.Button(self.student_frame, text="还书", command=self.return_book)
        self.return_book.grid(row=5, column=0, pady=20)

        self.student_logout_button = tk.Button(self.student_frame, text="退出登录", command=self.logout)
        self.student_logout_button.grid(row=6, column=1, columnspan=4, pady=20)

        self.root.mainloop()
        self.output_window.run()

    def login(self):
        username = self.username_entry.get()
        password = self.password_entry.get()

        if self.lms.login(username, password, "admin"):
            self.login_frame.pack_forget()
            self.admin_frame.pack()
        elif self.lms.login(username, password, "student"):
            self.login_frame.pack_forget()
            self.student_frame.pack()

    def register(self):
        self.popup = tk.Toplevel(self.root)
        self.popup.title("学生注册")
        self.popup.geometry("300x150")

        tk.Label(self.popup, text="借阅证号:").grid(row=0, column=0)
        self.re_student_id_entry = tk.Entry(self.popup)
        self.re_student_id_entry.grid(row=0, column=1)

        tk.Label(self.popup, text="姓名:").grid(row=1, column=0)
        self.re_name_entry = tk.Entry(self.popup)
        self.re_name_entry.grid(row=1, column=1)

        tk.Label(self.popup, text="密码:").grid(row=2, column=0)
        self.re_password_entry = tk.Entry(self.popup, show="*")
        self.re_password_entry.grid(row=2, column=1)

        self.register_button = tk.Button(self.popup, text="确认注册", command=self.register_student)
        self.register_button.grid(row=3, columnspan=3)

    def register_student(self):
        student_id = self.re_student_id_entry.get()
        name = self.re_name_entry.get()
        password = self.re_password_entry.get()
        self.re_student_id_entry.delete(0, tk.END)
        self.re_name_entry.delete(0, tk.END)
        self.re_password_entry.delete(0, tk.END)
        self.lms.register_student(student_id, name, student_id, password)
        self.popup.destroy()

    def logout(self, ):

        self.admin_frame.pack_forget()
        self.student_frame.pack_forget()
        self.login_frame.pack()
        self.output_window.clear_text()
        print("账号已登出")
        print("输出：")
        self.username_entry.delete(0, "end")
        self.password_entry.delete(0, "end")

    def add_book(self):
        book_id = int(self.book_id_entry.get())
        title = self.title_entry.get()
        author = self.author_entry.get()
        price = float(self.price_entry.get())
        publisher = self.publisher_entry.get()
        quantity = int(self.quantity_entry.get())
        category = self.category_entry.get()
        self.book_id_entry.delete(0, tk.END)
        self.title_entry.delete(0, tk.END)
        self.author_entry.delete(0, tk.END)
        self.price_entry.delete(0, tk.END)
        self.publisher_entry.delete(0, tk.END)
        self.quantity_entry.delete(0, tk.END)
        self.category_entry.delete(0, tk.END)

        self.lms.add_book(book_id, title, author, price, publisher, quantity, category)
        messagebox.showinfo("提示", "图书添加成功")

    def delete_book(self):
        book_id = int(self.delete_book_id_entry.get())
        self.delete_book_id_entry.delete(0, tk.END)
        self.lms.delete_book(book_id)
        messagebox.showinfo("提示", "图书删除成功")

    def show_records(self):
        self.lms.show_borrow_records()

    def search_books(self):
        keyword = self.search_books_keyword_entry.get()
        self.search_books_keyword_entry.delete(0, tk.END)
        search_type = self.search_books_type_combobox.get()
        if search_type == "书名":
            search_type = 'title'
        elif search_type == "作者":
            search_type = 'author'
        elif search_type == "价格":
            search_type = 'price'
        elif search_type == "出版社":
            search_type = 'publisher'
        self.lms.search_books(keyword, search_type)

    def search_students(self):
        keyword = self.search_students_keyword_entry.get()
        search_type = self.search_students_type_combobox.get()
        self.search_students_keyword_entry.delete(0, tk.END)
        if search_type == "姓名":
            search_type = 'name'
        else:
            search_type = 'username'

        self.lms.search_students(keyword, search_type)

    def search_borrow_records(self):
        student_id = int(self.search_borrow_records_student_id_entry.get())
        self.search_borrow_records_student_id_entry.delete(0, tk.END)
        self.lms.search_borrow_records_by_student_id(student_id)

    def search_book_quantity(self):
        self.lms.search_book_quantity_by_category()

    def borrow(self):
        book_id = self.borrow_entry.get()
        self.borrow_entry.delete(0, tk.END)
        self.lms.borrow_book(book_id)

    def return_book(self):
        book_id = self.return_entry.get()
        self.return_entry.delete(0, tk.END)
        self.lms.return_book(book_id)

    def return_admin(self):
        popup = tk.Toplevel(self.root)
        popup.title("逾期还书")

        tk.Label(popup, text="借阅证号:").grid(row=0, column=0)
        self.student_id_entry = tk.Entry(popup)
        self.student_id_entry.grid(row=0, column=1)

        tk.Label(popup, text="书号:").grid(row=1, column=0)
        self.book_id_entry = tk.Entry(popup)
        self.book_id_entry.grid(row=1, column=1)

        self.returnadmin_button = tk.Button(popup, text="确认还书", command=self.return_admin1)
        self.returnadmin_button.grid(row=2, columnspan=1)

    def return_admin1(self):
        student_id = self.student_id_entry.get()
        book_id = self.book_id_entry.get()
        self.student_id_entry.delete(0, tk.END)
        self.book_id_entry.delete(0, tk.END)
        self.lms.return_book_admin(student_id, book_id)

    def grant0(self):
        popup = tk.Toplevel(self.root)
        popup.title("管理员权限授予")

        tk.Label(popup, text="借阅证号:").grid(row=0, column=0)
        self.student_id_entry = tk.Entry(popup)
        self.student_id_entry.grid(row=0, column=1)

        self.grant_button = tk.Button(popup, text="确认", command=self.grant1)
        self.grant_button.grid(row=1, columnspan=1)

    def grant1(self):
        student_id = self.student_id_entry.get()
        self.student_id_entry.delete(0, tk.END)
        self.lms.grant_stu(student_id)

    def show_book(self):
        self.lms.show_books()

    def show_stus(self):
        self.lms.show_student()

    def edit_book(self):
        self.real_book_id = int(self.edit_book_id_entry.get())

        cursor = self.lms.conn.cursor()
        # 执行 SQL 查询语句
        cursor.execute('SELECT * FROM Books WHERE book_id=?', (self.real_book_id,))
        book = cursor.fetchall()
        self.lms.conn.commit()

        self.master = tk.Toplevel(self.root)
        self.master.title("修改图书信息")

        # 创建标签和文本框
        tk.Label(self.master, text="书籍编号:").grid(row=0)
        self.book_id_entry = tk.Entry(self.master)
        self.book_id_entry.grid(row=0, column=1)

        tk.Label(self.master, text="书名:").grid(row=1)
        self.title_entry = tk.Entry(self.master)
        self.title_entry.grid(row=1, column=1)

        tk.Label(self.master, text="作者:").grid(row=2)
        self.author_entry = tk.Entry(self.master)
        self.author_entry.grid(row=2, column=1)

        tk.Label(self.master, text="价格:").grid(row=3)
        self.price_entry = tk.Entry(self.master)
        self.price_entry.grid(row=3, column=1)

        tk.Label(self.master, text="出版社:").grid(row=4)
        self.publisher_entry = tk.Entry(self.master)
        self.publisher_entry.grid(row=4, column=1)

        tk.Label(self.master, text="数量:").grid(row=5)
        self.quantity_entry = tk.Entry(self.master)
        self.quantity_entry.grid(row=5, column=1)

        tk.Label(self.master, text="类别:").grid(row=6)
        self.category_entry = tk.Entry(self.master)
        self.category_entry.grid(row=6, column=1)
        for row in book:
            self.book_id_entry.insert(0, row[0])
            self.title_entry.insert(0, row[1])
            self.author_entry.insert(0, row[2])
            self.real_author = row[2]
            self.price_entry.insert(0, row[3])
            self.publisher_entry.insert(0, row[4])
            self.quantity_entry.insert(0, row[5])
            self.category_entry.insert(0, row[6])


        # 创建“修改”按钮
        self.update_button = tk.Button(self.master, text="修改", command=self.edit_book1)
        self.update_button.grid(row=7, column=1, pady=10)

    def edit_book1(self):
        book_id = int(self.book_id_entry.get())
        title = self.title_entry.get()
        author = self.author_entry.get()
        price = float(self.price_entry.get())
        publisher = self.publisher_entry.get()
        quantity = int(self.quantity_entry.get())
        category = self.category_entry.get()
        if self.real_book_id == book_id and self.real_author == author:
            self.book_id_entry.delete(0, tk.END)
            self.title_entry.delete(0, tk.END)
            self.author_entry.delete(0, tk.END)
            self.price_entry.delete(0, tk.END)
            self.publisher_entry.delete(0, tk.END)
            self.quantity_entry.delete(0, tk.END)
            self.category_entry.delete(0, tk.END)
            self.lms.edit_book(book_id, title, author, publisher, price, quantity, category)
            self.master.destroy()
        else:
            print("图书编号或作者不允许更改！请重新修改！")

    def delete_stu(self):
        stu_id = int(self.delete_stu_entry.get())
        self.delete_stu_entry.delete(0, tk.END)
        self.lms.delete_student(stu_id)

    def exit(self):
        self.root.destroy()
        self.output_window.destroy()


# 创建图书借阅管理系统对象
lms = LibraryManagementSystem()

# 创建图形化界面对象
gui = GUI(lms)
